﻿using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;

namespace FlashPay.DAO.Impl
{
    using FlashPay.DAO.Shared;
    using FlashPay.DAO.Interface;
    using FlashPay.EF;
    using FlashPay.EF.Models;
    

    using FlashPay.Entity.Parameter;
    using FlashPay.Entity.Response.Payment;
    using FlashPay.Entity;
    using FlashPay.Entity.Enum;
    using Microsoft.EntityFrameworkCore;

    /// <summary>
    /// 余额变化数据接口实现
    /// </summary>
    /// <remarks>2018-07-06 immi 创建</remarks>
    public class AdjustBalanceDaoImpl : BaseDAO, AdjustBalanceDao
    {
        #region 注入
        /// <summary>
        /// EF上下文
        /// </summary>
        private FlashPayContext _context { set; get; }

        /// <summary>
        /// 默认当前月份
        /// </summary>
        /// <param name="context"></param>
        public AdjustBalanceDaoImpl(FlashPayContext context)
        {
            _context = context;
        }

        public void Dispose()
        {
            if (_context != null)
            {
                _context.Dispose();
            }
        }
        #endregion

        /// <summary>
        /// 获取订单月份
        /// </summary>
        /// <param name="month"></param>
        /// <returns></returns>
        private string GetOrderMonth(long orderNo)
        {
            return orderNo.ToString().Substring(4, 2);
        }

        /// <summary>
        /// 根据编号获取余额变化记录
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns>Company</returns>
        public AdjustBalance Get(long id) {

            var tableName = $"AdjustBalance_{DateTime.Now.ToString("MM")}";

            var month = GetOrderMonth(id);

            if (month != DateTime.Now.ToString("MM"))
            {
                tableName = $"AdjustBalance_{month}";
            }

            var sql = $"SELECT * FROM {tableName} WHERE Rid={id}";

            var adjustBalance = _context.AdjustBalance
                .FromSql(sql)
                .AsNoTracking()
                .SingleOrDefault();

            return adjustBalance;
            //return _context.AdjustBalance.Where(x => x.Rid == id).FirstOrDefault();

        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="userInfo">新增对象</param>
        /// <returns></returns>
        public long Add(AdjustBalance model)
        {
            _context.AdjustBalance.Add(model);
            _context.SaveChanges();
            return model.Rid;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="CardMerchant">修改对象</param>
        /// <returns></returns>
        public bool Update(AdjustBalance model)
        {
            bool result = false;

            var adjustBalance = _context.AdjustBalance.Find(model.Rid);
            if (adjustBalance != null)
            {
                _context.SaveChanges();
                result = true;
            }
            return result;
        }

        /// <summary>
        /// 更新状态
        /// </summary>
        /// <param name="id">系统编号</param>
        /// <param name="status">状态</param>
        /// <returns></returns>
        public bool UpdateStatus(int id, SByte status)
        {
            //官方推荐的修改方式（先查询，再修改）
            var model = _context.AdjustBalance.Where(p => p.Rid.Equals(id)).FirstOrDefault();
            if (model != null)
            {
                model.AdjustStatus = status;
                int row = _context.SaveChanges();
                return true;
            }
            else
            {
                throw new Exception("当前记录不存在！");
            }
        }

        /// <summary>
        /// 获取所有记录
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns>List<UserInfo></returns>
        public List<AdjustBalance> GetList(AdjustBalanceQuery query)
        {
            //多条件查询
            var where = PredicateBuilder.True<AdjustBalance>();

            var list = _context.AdjustBalance.Where(where.Compile()).ToList();

            return list;
        }

        /// <summary>
        /// 收、付、中款卡余额变化查询
        /// </summary>
        /// <param name="query">参数</param>
        public DataGrid<PaymentAdjustBalanceResponse> GetPaymentAdjustBalancePager(PaymentAdjustBalanceQuery query)
        {
            //表名
            //var tableName = $"AdjustBalance_{Convert.ToDateTime(query.StartTime).ToString("MM")}";
            //var querySql = $"SELECT * FROM {tableName} where AdjustStatus = {query.AdjustStatus} and CreateDbdate> '{query.StartTime}' and CreateDbdate <= '{query.EndTime}' and CompanyId in({string.Join(",", query.CompanyIds)})";

            //公司编号
            var companyIds = string.Join(", ", query.CompanyIds);

            var sql = $"call sp_SelectAdjustBalance('{query.StartTime}', '{query.EndTime}', '{companyIds}');";

            var adjustBalances = _context.AdjustBalance
                .FromSql(sql)
                .OrderByDescending(x => x.CreateDbdate)
                .ToList();

            var q = from ab in adjustBalances
                    join bc in _context.BankCard on ab.CardBcid equals bc.Bcid
                    join c in _context.Company on ab.CompanyId equals c.CompanyId
                    select new
                    {
                        ab.CompanyId,
                        ab.Amount,
                        ab.AfterBalance,
                        ab.BeforeBalance,
                        c.CompanyName,
                        c.CompanyNameEn,
                        bc.CreateDate,
                        bc.BankName,
                        bc.CardNumber,
                        bc.CardName,
                        ab.CreateName,
                        ab.CreateDbdate,
                        ab.CreateRemark,
                        ab.AdjustStatus,
                        ab.OrderNo
                    };
            //银行卡启用状态
            if (query.CompanyIds != null && query.CompanyIds.Any())
            {
                q = q.Where(c => query.CompanyIds.Contains(c.CompanyId));
            }
            //公司名称
            if (!string.IsNullOrEmpty(query.CompanyName)) {
                q = q.Where(c => c.CompanyName.Contains(query.CompanyName));
            }
            //银行卡号
            if (!string.IsNullOrEmpty(query.CardNumber))
            {
                q = q.Where(c => c.CardNumber.Contains(query.CardNumber));
            }
            //银行名称
            if (!string.IsNullOrEmpty(query.BankName))
            {
                q = q.Where(c => c.BankName.Contains(query.BankName));
            }
            //银行卡用戶名
            if (!string.IsNullOrEmpty(query.CardName))
            {
                q = q.Where(c => c.CardName.Contains(query.CardName));
            }
            if (query.AdjustStatus.HasValue) {
                q = q.Where(c => c.AdjustStatus == query.AdjustStatus);
            }
            //分页
            var list = q.OrderByDescending(e=> e.CreateDbdate).Skip((query.Page.Value - 1) * query.Rows.Value).Take(query.Rows.Value).ToList();

            #region 模型转换
            var paymentAdjustBalanceResponses = new List<PaymentAdjustBalanceResponse>();

            list.ForEach(item => {

                var paymentAdjustBalanceResponse = new PaymentAdjustBalanceResponse()
                {
                    CompanyName = item.CompanyName,
                    CompanyNameEn = item.CompanyNameEn,
                    Amount = item.Amount,
                    BeforeBalance = item.BeforeBalance,
                    AfterBalance = item.AfterBalance,
                    BankName = item.BankName,
                    CardNumber = Util.WebUtil.SetCardNumber(item.CardNumber),
                    CardName=item.CardName,
                    CreateName = item.CreateName,
                    CreateDBDate = item.CreateDbdate.ToString("yyyy-MM-dd HH:mm:ss"),
                    CreateRemark = item.CreateRemark,
                    AdjustStatus = item.AdjustStatus,
                    OrderNo = item.OrderNo.HasValue? item.OrderNo.Value.ToString():""
                };

                paymentAdjustBalanceResponses.Add(paymentAdjustBalanceResponse);
            });
            #endregion

            return new DataGrid<PaymentAdjustBalanceResponse>()
            {
                Total = q.Count(),
                Rows = paymentAdjustBalanceResponses,
                Success = true,
            };
        }

        /// <summary>
        /// 收、付、中款卡余额变化查询导出
        /// </summary>
        /// <param name="query">参数</param>
        public List<PaymentAdjustBalanceResponse> GetPaymentAdjustBalanceExport(PaymentAdjustBalanceQuery query)
        {
            //公司编号
            var companyIds = string.Join(", ", query.CompanyIds);

            var sql = $"call sp_SelectAdjustBalance('{query.StartTime}', '{query.EndTime}', '{companyIds}');";

            var adjustBalances = _context.AdjustBalance
                .FromSql(sql)
                .OrderByDescending(x => x.CreateDbdate)
                .ToList();

            var q = from ab in adjustBalances
                    join bc in _context.BankCard on ab.CardBcid equals bc.Bcid
                    join c in _context.Company on ab.CompanyId equals c.CompanyId
                    select new
                    {
                        ab.CompanyId,
                        ab.Amount,
                        ab.AfterBalance,
                        ab.BeforeBalance,
                        c.CompanyName,
                        c.CompanyNameEn,
                        bc.CreateDate,
                        bc.BankName,
                        bc.CardNumber,
                        bc.CardName,
                        ab.CreateName,
                        ab.CreateDbdate,
                        ab.CreateRemark,
                        ab.AdjustStatus,
                        ab.OrderNo
                    };
            //银行卡启用状态
            if (query.CompanyIds != null && query.CompanyIds.Any())
            {
                q = q.Where(c => query.CompanyIds.Contains(c.CompanyId));
            }
            //公司名称
            if (!string.IsNullOrEmpty(query.CompanyName))
            {
                q = q.Where(c => c.CompanyName.Contains(query.CompanyName));
            }
            //银行卡号
            if (!string.IsNullOrEmpty(query.CardNumber))
            {
                q = q.Where(c => c.CardNumber.Contains(query.CardNumber));
            }
            //银行名称
            if (!string.IsNullOrEmpty(query.BankName))
            {
                q = q.Where(c => c.BankName.Contains(query.BankName));
            }
            //银行卡用戶名
            if (!string.IsNullOrEmpty(query.CardName))
            {
                q = q.Where(c => c.CardName.Contains(query.CardName));
            }
            if (query.AdjustStatus.HasValue)
            {
                q = q.Where(c => c.AdjustStatus == query.AdjustStatus);
            }
            //分页
            var list = q.OrderByDescending(e => e.CreateDbdate).ToList();

            #region 模型转换
            var paymentAdjustBalanceResponses = new List<PaymentAdjustBalanceResponse>();

            list.ForEach(item => {

                var paymentAdjustBalanceResponse = new PaymentAdjustBalanceResponse()
                {
                    CompanyName = item.CompanyName,
                    CompanyNameEn = item.CompanyNameEn,
                    Amount = item.Amount,
                    BeforeBalance = item.BeforeBalance,
                    AfterBalance = item.AfterBalance,
                    BankName = item.BankName,
                    CardNumber = Util.WebUtil.SetCardNumber(item.CardNumber),
                    CardName = item.CardName,
                    CreateName = item.CreateName,
                    CreateDBDate = item.CreateDbdate.ToString("yyyy-MM-dd HH:mm:ss"),
                    CreateRemark = item.CreateRemark,
                    AdjustStatus = item.AdjustStatus,
                    OrderNo = item.OrderNo.HasValue ? item.OrderNo.Value.ToString() : ""
                };

                paymentAdjustBalanceResponses.Add(paymentAdjustBalanceResponse);
            });
            #endregion

            return paymentAdjustBalanceResponses;
        }
    }
}
